#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Datetime: 2022/9/23 15:34
# @Author  : CHENWang
# @Site    : 
# @File    : trading_data_analysis.py
# @Software: PyCharm

"""
脚本说明: trading_data数据清洗，加工，分析
"""

import os
import time
import datetime
import numpy as np
import pandas as pd
from dateutil.tz import tzutc
import yfinance as yf
from openpyxl import load_workbook
from quant_researcher.quant.project_tool import hammer
from quant_researcher.quant.project_tool.localize import DATA_DIR
from quant_researcher.quant.project_tool.wrapper_tools.common_wrappers import deco_retry
from quant_researcher.quant.project_tool.time_tool import get_yesterday, get_today, str_to_timestamp, date_shifter, calc_date_diff, \
    get_specific_weekday_in_range, get_specific_weekday_of_each_quarter_last_month, get_quarter_end, get_the_end_of_this_month
from quant_researcher.quant.project_tool.file_tool import copy_file, get_all_filename_path
from quant_researcher.quant.datasource_fetch.crypto_api.glassnode import get_prices, get_ret
from quant_researcher.quant.datasource_fetch.crypto_api.binance import fetch_binance_kline
from quant_researcher.quant.datasource_fetch.crypto_api.bitfinex import fetch_bitfinex_kline
from quant_researcher.quant.datasource_fetch.crypto_api.okex.okx.Market_api import marketAPI
from quant_researcher.quant.project_tool.plot_tool import plot_ohlcv
from quant_researcher.quant.datasource_fetch.crypto_api.coingecko.coingecko import CoinGeckoAPI
from quant_researcher.quant.datasource_fetch.crypto_api.coinmarketcap import get_recent7_ohlcvm_via_http, get_asset_ohlcvm_via_http, get_total_marketcap_amount_via_http
from quant_researcher.quant.datasource_fetch.crypto_api.self_defined import true_stalecoin_crypto
from quant_researcher.quant.datasource_fetch.crypto_api.coinglass import get_recent_funding_rate, get_gbtc_premium
from quant_researcher.quant.project_tool.db_operator.my_excel import df_list_2_excel_sheets


def okx_margin_interest_rate_cleaning():
    # 清洗从okx那边要到的杠杆交易借贷费率
    file_path = os.path.join(DATA_DIR, f'margin_interest_rate')
    file_name = os.path.join(file_path, f'okx 20211201-20220916')
    okx_margin_fee = pd.read_csv(f'{file_name}.csv')
    okx_margin_fee['end_date'] = pd.to_datetime(okx_margin_fee['create_time'])
    okx_margin_fee['end_date'] = okx_margin_fee['end_date'].dt.strftime("%Y-%m-%d")
    okx_usdt_margin_fee = okx_margin_fee[okx_margin_fee['currency'] == 'USDT'].groupby('end_date')['rate'].mean()
    okx_usdt_margin_fee.name = 'usdt'
    okx_eth_margin_fee = okx_margin_fee[okx_margin_fee['currency'] == 'ETH'].groupby('end_date')['rate'].mean()
    okx_eth_margin_fee.name = 'eth'
    okx_btc_margin_fee = okx_margin_fee[okx_margin_fee['currency'] == 'BTC'].groupby('end_date')['rate'].mean()
    okx_btc_margin_fee.name = 'btc'

    # 获取BTC_ohlcv数据
    file_name = os.path.join(DATA_DIR, f'BTC_history_ohlcvm')
    ohlcv_data = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    prices_df = ohlcv_data[['close']]
    prices_df['log_price'] = np.log10(prices_df['close'])

    okx_usdt_margin_fee = pd.concat([okx_usdt_margin_fee, okx_eth_margin_fee, okx_btc_margin_fee, prices_df], axis=1)
    file_name = os.path.join(file_path, f'okx_margin_interest_rate_official')
    okx_usdt_margin_fee.to_excel(f'{file_name}.xlsx')


def etf_flow_analysis():
    # 分析 Digital_Asset_Fund_Flows_weekly
    file_path = os.path.join(DATA_DIR, f'etf_analysis')
    file_name = os.path.join(file_path, f'Digital_Asset_Fund_Flows_weekly')
    fund_flows = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    fund_flows.index = fund_flows.index.strftime('%Y-%m-%d')

    # 获取BTC_ohlcv数据
    file_name = os.path.join(DATA_DIR, f'BTC_history_ohlcvm')
    ohlcv_data = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    prices_df = ohlcv_data[['close']]
    prices_df['log_price'] = np.log10(prices_df['close'])

    all_df = pd.concat([fund_flows, prices_df], axis=1, join='inner')
    all_df.sort_index(inplace=True)
    all_df['chg'] = all_df['close'].pct_change()
    file_name = os.path.join(file_path, f'Digital_Asset_Fund_Flows_Analysis')
    all_df.to_excel(f'{file_name}.xlsx')


def turnover_analysis():
    file_path = os.path.join(DATA_DIR, f'trading_data')
    # 获取全市场交易额
    file_name = os.path.join(file_path, f'cex_total_spot_amount_coinmarketcap')
    cex_total_spot_amount = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    cex_total_spot_amount.loc[cex_total_spot_amount['amount']<=0, 'amount'] = np.NaN
    cex_total_spot_amount.ffill(inplace=True)

    file_name = os.path.join(file_path, f'dex_total_spot_amount_coingecko')
    dex_total_spot_amount = pd.read_excel(f'{file_name}.xlsx', index_col='date')
    cex_dex_total_spot_amount = cex_total_spot_amount.copy()
    cex_dex_total_spot_amount['dex_total'] = dex_total_spot_amount['total']
    cex_dex_total_spot_amount['dex_total'] = cex_dex_total_spot_amount['dex_total'].fillna(0)
    cex_dex_total_spot_amount.loc[cex_dex_total_spot_amount['dex_total']<0, 'dex_total'] = np.NaN
    cex_dex_total_spot_amount.ffill(inplace=True)
    cex_dex_total_spot_amount['amount'] = cex_dex_total_spot_amount['amount'] + cex_dex_total_spot_amount['dex_total']

    # 获取BTC_ohlcv数据
    file_name = os.path.join(DATA_DIR, f'BTC_history_ohlcvm')
    cex_btc_spot_amount = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')

    for i, temp_df in enumerate([cex_total_spot_amount, cex_btc_spot_amount, cex_dex_total_spot_amount]):
        temp_df['turnover_ratio'] = temp_df['amount'] / temp_df['market_cap']
        temp_df['turnover_ratio_ma7'] = temp_df['turnover_ratio'].rolling(7).mean()
        temp_df['turnover_ratio_ma30'] = temp_df['turnover_ratio'].rolling(30).mean()

        temp_df = temp_df.loc['2015-01-01':, :]
        if i == 0:
            file_name = os.path.join(file_path, f'turnover_analysis_cex_total')
        elif i == 1:
            file_name = os.path.join(file_path, f'turnover_analysis_cex_btc')
        elif i == 2:
            file_name = os.path.join(file_path, f'turnover_analysis_cex_dex_total')
        temp_df.to_excel(f'{file_name}.xlsx')


def trading_volume_analysis():
    # 获取全市场交易额
    file_path = os.path.join(DATA_DIR, f'trading_data')
    file_name = os.path.join(file_path, f'cex_total_spot_amount_coinmarketcap')
    all_market_volume = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')

    all_market_volume['amount_ma7'] = all_market_volume['amount'].rolling(7).mean()
    all_market_volume['amount_ma14'] = all_market_volume['amount'].rolling(14).mean()
    all_market_volume['amount_ma30'] = all_market_volume['amount'].rolling(30).mean()

    # 交易额布林带分析
    for ma_period in [7, 14, 30]:
        for period in [100, 150, 200, 300]:
            all_market_volume[f'amount_ma{ma_period}_bolling{period}'] = (all_market_volume[f'amount_ma{ma_period}'] - all_market_volume[f'amount_ma{ma_period}'].rolling(period).mean()) / \
                                                                     all_market_volume[f'amount_ma{ma_period}'].rolling(period).std()

    # 获取BTC_ohlcv数据
    file_name = os.path.join(DATA_DIR, f'BTC_history_ohlcvm')
    cex_btc_spot_amount = pd.read_excel(f'{file_name}.xlsx', index_col='end_date')
    prices_df = cex_btc_spot_amount[['close']]
    prices_df['log_price'] = np.log10(prices_df['close'])

    all_market_volume_price_df = pd.concat([all_market_volume, prices_df], axis=1)
    all_market_volume_price_df.sort_index(inplace=True)
    all_market_volume_price_df = all_market_volume_price_df.loc['2015-01-01':, :]
    file_name = os.path.join(file_path, f'cex_total_spot_amount_coinmarketcap_analysis')
    all_market_volume_price_df.to_excel(f'{file_name}.xlsx')


if __name__ == '__main__':
    etf_flow_analysis()
    # turnover_analysis()

    trading_volume_analysis()

    today = get_today(marker='with_n_dash')
    prices_df = get_prices(ohlc=True, asset='BTC', start_date='2012-01-01', end_date=today, interval='24h')
    prices_df['log_price'] = np.log10(prices_df['close'])
    file_path = os.path.join(DATA_DIR, f'trading_data')
    file_name = os.path.join(file_path, f'glassnode_btcusdt_daily_ohlcv')
    prices_df.to_excel(f'{file_name}.xlsx')

    okx_margin_interest_rate_cleaning()